package com.example.dmweb.chat.chatService.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.rendering.PDFRenderer;
import org.apache.pdfbox.text.PDFTextStripper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;

@Slf4j
public class Excel2Txt {

    public String excel2txt(InputStream fis) throws IOException {
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        StringBuilder sb = new StringBuilder();

        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                Cell cell = row.getCell(colIndex);

                String cellValue = getCellValue(cell);
                cellValue = cellValue.replaceAll("\\r|\\n", "");

                if (cell != null) {
                    sb.append(cellValue);
                } else {
                    sb.append("");
                }

                // 如果当前单元格是合并单元格的开始
                if (isMergedRegion(sheet, rowIndex, colIndex)) {
                    int lastColumn = getMergedRegionLastColumn(sheet, rowIndex, colIndex);
                    colIndex = lastColumn; // 跳过合并区域
                }

                if (colIndex < row.getLastCellNum() - 1) {
                    sb.append("|");
                }
            }

            sb.append("\n");
        }
        log.info("excel提取完成");
        return String.valueOf(sb);
    }
    public List<String> pdf2Pic(InputStream fis) {
        List<String> byteArrayList = new ArrayList<>();
        try {
            // 加载PDF文件
            PDDocument document = PDDocument.load(fis);

            // 创建PDF渲染器
            PDFRenderer pdfRenderer = new PDFRenderer(document);
            // 循环遍历每一页
            for (int pageNumber = 0; pageNumber < document.getNumberOfPages(); ++pageNumber) {
                // 渲染页面为BufferedImage
                BufferedImage bim = pdfRenderer.renderImageWithDPI(pageNumber, 300);
                // 将BufferedImage转换为字节数组
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                ImageIO.write(bim, "png", baos);
                byte[] byteArray = baos.toByteArray();
                byteArrayList.add(Base64.getEncoder().encodeToString(byteArray));
            }

            // 关闭文档
            document.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return byteArrayList;
    }
    public static void main2(String[] args) {
        String pdfFilePath = "D:\\other\\work\\虚拟电厂-AI能量助手\\电费账单\\其他电费账单\\安徽腾辉物业\\腾辉物业2023.7电费清单.pdf"; // PDF文件路径
        String txtFilePath = "D:\\other\\work\\虚拟电厂-AI能量助手\\电费账单\\其他电费账单\\安徽腾辉物业\\腾辉物业2023.7电费清单.txt"; // TXT文件路径

        try {
            // 加载PDF文档
            PDDocument document = PDDocument.load(new File(pdfFilePath));

            // 创建PDFTextStripper对象
            PDFTextStripper pdfStripper = new PDFTextStripper();

            // 获取PDF文档的内容
            String text = pdfStripper.getText(document);

            // 创建文件写入器
            FileWriter writer = new FileWriter(txtFilePath);

            // 按行写入TXT文件
            writer.write(text);

            // 关闭写入器
            writer.close();

            // 关闭PDF文档
            document.close();

            System.out.println("PDF内容提取成功并保存到TXT文件中。");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {

        // 获取PDF文件路径
        String pdfFilePath = "D:\\dataAll\\新语料文件6.13\\20240613新增语料\\北京电力市场交易工作问答.pdf";

        try {
            // 加载PDF文件
            PDDocument document = PDDocument.load(new File(pdfFilePath));

            // 创建PDF渲染器
            PDFRenderer pdfRenderer = new PDFRenderer(document);

            // 循环遍历每一页
            for (int pageNumber = 0; pageNumber < document.getNumberOfPages(); ++pageNumber) {
                // 渲染页面为BufferedImage
                BufferedImage bim = pdfRenderer.renderImageWithDPI(pageNumber, 300);

                // 输出图片文件
                String imageFilePath = pdfFilePath + "_page_" + (pageNumber + 1) + ".png";
                ImageIO.write(bim, "png", new File(imageFilePath));
                System.out.println("Converted page " + (pageNumber + 1) + " to: " + imageFilePath);
            }

            // 关闭文档
            document.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static void main1(String[] args) {
        String excelFilePath = "C:\\Users\\29634\\Downloads\\general-table (76).xlsx";
        String txtFilePath = "D:\\other\\work\\虚拟电厂-AI能量助手\\电费账单\\结果\\福建项目\\长乐产投\\general-table (76).txt";

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis);
             BufferedWriter writer = new BufferedWriter(new FileWriter(txtFilePath))) {

            Sheet sheet = workbook.getSheetAt(0);
            StringBuilder sb = new StringBuilder();

            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);

                    String cellValue = getCellValue(cell);
                    cellValue = cellValue.replaceAll("\\r|\\n", "");

                    if (cell != null) {
                        sb.append(cellValue);
                    } else {
                        sb.append("");
                    }

                    // 如果当前单元格是合并单元格的开始
                    if (isMergedRegion(sheet, rowIndex, colIndex)) {
                        int lastColumn = getMergedRegionLastColumn(sheet, rowIndex, colIndex);
                        colIndex = lastColumn; // 跳过合并区域
                    }

                    if (colIndex < row.getLastCellNum() - 1) {
                        sb.append("|");
                    }
                }

                sb.append("\n");
            }

            writer.write(sb.toString());

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(row, column)) {
                return true;
            }
        }
        return false;
    }

    private static int getMergedRegionLastColumn(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(row, column)) {
                return range.getLastColumn();
            }
        }
        return column;
    }
}
